【精选】MYSQL 行转列、列转行、多列转一行、一行转多列 | 您所在的位置:网站首页 › 动物小插画 萌萌哒图片 › 【精选】MYSQL 行转列、列转行、多列转一行、一行转多列 |
本篇博客是之前看到关于面试的一道SQL题 拿来学习了一下 看完看完本篇博客,相信你会对SQL语法有不一样的感觉和认识。 版本说明 MySQL8.0 🏆一、行转列 ⭐️需求select name, max(case subject when '语文' then resuilt else 0 end) '语文', max(case subject when '数学' then resuilt else 0 end) '数学', max(case subject when '物理' then resuilt else 0 end) '物理' from student group by name 大体同case when select name, max(if(subject = '语文',resuilt,0)) '语文' from student group by name set @sql = null; select group_concat(distinct concat('max(if(a.subject = ''',a.subject,''', a.resuilt, 0)) as ''',a.subject, '''')) into @sql from student a; set @sql = concat('select name,', @sql, 'from student a group by a.name' ); prepare stmt from @sql; -- 动态生成脚本,预备一个语句 execute stmt; -- 动态执行脚本,执行预备的语句 deallocate prepare stmt; -- 释放预备的语句 扩展分析 mysql的列转行为什么一定要用sum 或者max 遇到这种问题我们可以通过调试sql,来解决我们遇到的疑惑。 🏆二、列转行 ⭐️需求将 转换为 select name, '语文' as subject, chinese as resuilt from -- 注意这里正常应该是from后直接跟一个表 偷懒不想创建表结构 直接用的子查询(行转列的代码) (select name, max(case subject when '语文' then resuilt else 0 end) 'chinese', max(case subject when '数学' then resuilt else 0 end) 'math', max(case subject when '物理' then resuilt else 0 end) 'physics' from student group by name ) t union all select name, '数学' as subject, math as resuilt from -- 注意这里正常应该是from后直接跟一个表 偷懒不想创建表结构 直接用的子查询(行转列的代码) (select name, max(case subject when '语文' then resuilt else 0 end) 'chinese', max(case subject when '数学' then resuilt else 0 end) 'math', max(case subject when '物理' then resuilt else 0 end) 'physics' from student group by name ) t union all select name, '物理' as subject, physics as resuilt from -- 注意这里正常应该是from后直接跟一个表 偷懒不想创建表结构 直接用的子查询(行转列的代码) (select name, max(case subject when '语文' then resuilt else 0 end) 'chinese', max(case subject when '数学' then resuilt else 0 end) 'math', max(case subject when '物理' then resuilt else 0 end) 'physics' from student group by name ) t 总结 行转列,使用case…when分情况查询数据,group by和sum/max进行筛选 列转行,查询需要的每列数据使用union或者union all求并集 🏆 三、多列转一行 ⭐️需求将 变为 select name,GROUP_CONCAT(`subject`,':',resuilt) '成绩' from student group by name GROUP_CONCAT(expr)该函数将非空列值按照分组条件进行合并并最终返回。如果有空值,则返回为空 🏆四、一行转多列 ⭐️需求将 变为 a表 select name, marks, case when locate('语文',marks) > 0 then substring_index(substring_index(marks,'语文:',-1),',',1) else 0 end as 语文 , case when locate('数学',marks) > 0 then substring_index(substring_index(marks,'数学:',-1),',',1) else 0 end as 数学 , case when locate('物理',marks) > 0 then substring_index(substring_index(marks,'物理:',-1),',',1) else 0 end as 物理 , case when locate('历史',marks) > 0 then substring_index(substring_index(marks,'历史:',-1),',',1) else 0 end as 历史 from (select name, GROUP_CONCAT(subject,':',resuilt) marks from student group by name) a上面只是第一步 举例 要求还差一步 使用union all 继续加工sql b表 select name, '语文' subject, 语文 score from (a表) b union all select name, '数学' subject, 数学 score from (a表) b union all select name, '物理' subject, 物理 score from (a表) b union all select name, '历史' subject, 历史 score from (a表) bLocate函数主要的作用是判断一个字符串是否包含另一个字符串,如 Locate(str,sub) > 0,表示sub字符串包含str字符串; Locate(str,sub) = 0,表示sub字符串不包含str字符串。 substring_index(参数str,参数delim,参数count) str :要处理的字符串 delim:分隔符 count:计数 也就是说,如果count是正数,那么就是从左往右数,第N个分隔符的左边的全部内容! 相反,如果是负数,那么就是从右边开始数,第N个分隔符右边的所有内容, 参考文章 mysql行转列,列转行,以及为什么行转列的时候用max函数_一朵风中摇曳的水仙花的博客-CSDN博客_max函数 行转列 浅析MySQL中concat以及group_concat的使用 - 程序员大本营 MySQL函数Locate的使用_今夜无风亦无雨的博客-CSDN博客_mysql的locate MySQL中的substring_index()函数使用方法与技巧_极客小俊的博客-CSDN博客_mysql substring_index |
CopyRight 2018-2019 实验室设备网 版权所有 |